use sale;
-- 1
with tmp as (
 select team, year, year-rank() over(partition by team order by year) gid
 from t1
)
select team, count(1) times
from tmp
group by team, gid
having times>=3;

-- 2
with tmp as (
select id, time, price
  ,case when price<lag_price and price<lead_price then 'troughs'
        when price>lag_price and price>lead_price then 'crests'
        else null end flag
from(select id, time, price
        ,lag(price) over(partition by id order by cast(replace(time,':','') as int)) lag_price
        ,lead(price) over(partition by id order by cast(replace(time,':','') as int)) lead_price
     from t2
     ) tmp1
)
select id, time, price, flag
from tmp
where flag is not null;

-- 3.1
select id
  ,(unix_timestamp(max(dt), 'yyyy/MM/dd HH:mm')-unix_timestamp(min(dt), 'yyyy/MM/dd HH:mm'))/60 times
  ,count(1) steps
from t3
group by id;

--3.2
add jar /root/jar/mycount.jar;
create temporary function mycount as 'com.zwcdcc.count.Mycount';
with tmp as (
select id
 ,unix_timestamp(dt, 'yyyy/MM/dd HH:mm') unixstamp
 ,nvl((unix_timestamp(dt, 'yyyy/MM/dd HH:mm')-lag(unix_timestamp(dt, 'yyyy/MM/dd HH:mm')) over(partition by id order by dt))/60, 0) timediff
from t3
)
select id, (max(unixstamp)-min(unixstamp))/60 times, count(1) steps
from(select id, unixstamp, mycount(timediff) flag
     from tmp
    ) t
group by id, flag;